package com.hwapitest.utils;
import org.apache.log4j.Logger;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.List;



public class ExcelUtil {
    private static final Logger logger = Logger.getLogger(ExcelUtil.class);

    /**
     * 通用Excel读取方法
     */
    public static <T> List<T> loadExcel(String excelPath, String sheetName, Class<T> clazz) {
        logger.info("开始读取: " + sheetName);
        List<T> list = new ArrayList<>();

        try (InputStream in = getResourceAsStream(excelPath);
             Workbook workbook = WorkbookFactory.create(in)) {

            Sheet sheet = workbook.getSheet(sheetName);
            if (sheet == null) {
                logger.error("工作表不存在: " + sheetName);
                return list;
            }

            // 获取表头
            Row headerRow = sheet.getRow(0);
            if (headerRow == null) {
                logger.warn("工作表无表头行: " + sheetName);
                return list;
            }

            // 解析表头
            int columnCount = headerRow.getLastCellNum();
            String[] headers = new String[columnCount];
            for (int i = 0; i < columnCount; i++) {
                Cell cell = headerRow.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                headers[i] = toCamelCase(getCellStringValue(cell).trim());
            }

            // 处理数据行
            for (int rowIndex = 1; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
                Row row = sheet.getRow(rowIndex);
                if (row == null || isRowEmpty(row)) continue;

                T obj = clazz.getDeclaredConstructor().newInstance();
                boolean hasData = false;

                for (int colIndex = 0; colIndex < columnCount; colIndex++) {
                    Cell cell = row.getCell(colIndex, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
                    String value = getCellStringValue(cell).trim();

                    if (!value.isEmpty()) {
                        hasData = true;
                        setProperty(obj, headers[colIndex], value);
                    }
                }

                if (hasData) {
                    list.add(obj);
                }
            }

        } catch (Exception e) {
            logger.error("读取Excel失败: " + e.getMessage(), e);
            throw new RuntimeException("Excel解析错误", e);
        }

        logger.info("完成读取: " + sheetName + " (记录数: " + list.size() + ")");
        return list;
    }

    // =============== 辅助方法 ===============

    private static InputStream getResourceAsStream(String path) throws IOException {
        InputStream in = ExcelUtil.class.getClassLoader().getResourceAsStream(path);
        if (in == null) {
            throw new IOException("文件未找到: " + path);
        }
        return in;
    }

    private static String getCellStringValue(Cell cell) {
        switch (cell.getCellType()) {
            case NUMERIC:
                return NumberToTextConverter.toText(cell.getNumericCellValue());
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                try {
                    return cell.getStringCellValue();
                } catch (Exception e) {
                    return NumberToTextConverter.toText(cell.getNumericCellValue());
                }
            default:
                return cell.getStringCellValue();
        }
    }

    private static boolean isRowEmpty(Row row) {
        for (int i = 0; i < row.getLastCellNum(); i++) {
            Cell cell = row.getCell(i, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
            if (!getCellStringValue(cell).trim().isEmpty()) {
                return false;
            }
        }
        return true;
    }

    private static <T> void setProperty(T obj, String fieldName, String value) {
        String methodName = "set" + capitalize(fieldName);
        try {
            Method method = obj.getClass().getMethod(methodName, String.class);
            method.invoke(obj, value);
        } catch (Exception e) {
            logger.error("设置属性失败: " + fieldName + " -> " + methodName);
        }
    }

    private static String toCamelCase(String str) {
        if (str == null || str.isEmpty()) return str;

        StringBuilder sb = new StringBuilder();
        boolean capitalizeNext = false;

        for (char c : str.toCharArray()) {
            if (c == '_' || c == ' ') {
                capitalizeNext = true;
            } else {
                sb.append(capitalizeNext ? Character.toUpperCase(c) : c);
                capitalizeNext = false;
            }
        }
        return sb.toString();
    }

    private static String capitalize(String str) {
        if (str == null || str.isEmpty()) return str;
        return Character.toUpperCase(str.charAt(0)) + str.substring(1);
    }
}
